use master
go
create database ORDERS
on
(
   name='ORDERS',
   filename='F:\ORDERS.mdf',
   size=5MB,
   maxsize=50MB,
   filegrowth=10%
)
log on
(
   name='ORDERS_log',
   filename='F:\ORDERS_log.mdf',
   size=5MB,
   maxsize=50MB,
   filegrowth=10%
)
go

use ORDERS
go

create table orders
(
   orderId int primary key not null,
   orderDate datetime
)

create table orderItem
(
   ItemiD int not null identity(1,1),
   orderId int references orders(orderId) not null,
   itemType nvarchar(20) not null,
   itemName nvarchar(20) not null,
   theNumber int not null,
   theMoney int not null
)
go

insert into orders values (1,'2008-01-12'),(2,'2008-02-10'),(3,'2008-02-15'),(4,'2008-03-10')

insert into orderItem values
(1,'文具','笔',72,2),(1,'文具','尺',10,1),(1,'体育用品','篮球',1,56),
(2,'文具','笔',36,2),(2,'文具','固体胶',20,3),(2,'日常用品','透明胶',2,1),(2,'体育用品','羽毛球',20,3),
(3,'文具','笔',72,2),(3,'文具','笔',72,2),(3,'文具','笔',72,2),(4,'文具','笔',20,2),
(4,'文具','信纸',50,1),(4,'日常用品','毛巾',4,5),(4,'日常用品','透明胶',30,1),(4,'体育用品','羽毛球',20,3)
go

--1.查询所有订单订购的所有物品数量总和
select SUM(theNumber) 物品数量总和 from orderItem
--2.查询订单编号小于3的，平均单价小于10 每个订单订购的所有物品的数量和以及平均单价
select ItemiD 订单编号, SUM(theNumber)物品数量总和, AVG(theMoney)平均单价 from orderItem group by ItemiD having AVG(theMoney)<10 or ItemiD<3
--3.查询平均单价小于10并且总数量大于 50 每个订单订购的所有物品数量和以及平均单价
select SUM(theNumber)物品数量总和, AVG(theMoney)平均单价 from orderItem group by ItemiD having AVG(theMoney)<10 or SUM(theNumber)>50
--4.查询每种类别的产品分别订购了几次
select itemType 产品类别, COUNT(itemType) 次数 from orderItem group by itemType
--5.查询每种 类别的产品的订购总数量在100以上的订购总数量和平均单价
select AVG(theMoney)平均单价,SUM(theNumber)订购数量总和,itemType 产品类别 from orderItem group by itemType having SUM(theNumber)>100
--6.查询每种产品的订购次数，订购总数量和订购的平均单价
select itemName 产品名称,theNumber 订购次数, AVG(theMoney)平均单价,SUM(theNumber)订购数量总和 from orderItem group by itemName,theNumber